<?php

ob_start();
session_start();
include_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/configs/database.php');
/*

  /* Error reporting */
error_reporting(E_ALL ^ E_NOTICE);
ini_set('display_errors', TRUE);
ini_set('display_startup_errors', TRUE);

if (PHP_SAPI == 'cli')
    die('This example should only be run from a Web Browser');

/** Include PHPExcel */
require_once($_SERVER['DOCUMENT_ROOT'] . '/htqlmba/libraries/PHPExcel_1.7.9_doc/Classes/PHPExcel.php');
// Create new PHPExcel object
$objPHPExcel = new PHPExcel();

// Set document properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw")
        ->setLastModifiedBy("Maarten Balliauw")
        ->setTitle("Office 2007 XLSX Test Document")
        ->setSubject("Office 2007 XLSX Test Document")
        ->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.")
        ->setKeywords("office 2007 openxml php")
        ->setCategory("Test result file");
// Lay du lieu thong ke

$madv = $_SESSION['madv'];
$nhasx = $_SESSION['mansx'];
$nsx = $_SESSION['namsx'];
$loaimay = $_SESSION['maloai'];
	$madv = $_SESSION['madv'];
	$congsuat = $_SESSION['congsuat'];
	
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A1', 'Công Ty Điện Lực Cà Mau');
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A2', 'Địa chỉ: 22 Ngô Quyền P2 TP Cà Mau');
	$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('C1', 'CỘNG HÒA XÃ HỘI CHỦ NGHĨA VIỆT NAM');
	$objPHPExcel->setActiveSheetIndex(0)
						->setCellValue('C2', 'Độc Lập - Tự Do - Hạnh Phúc');
	$objPHPExcel->getActiveSheet()->mergeCells('A1:B1');
	$objPHPExcel->getActiveSheet()->mergeCells('A2:B2');
	$objPHPExcel->getActiveSheet()->mergeCells('C1:E1');
	$objPHPExcel->getActiveSheet()->mergeCells('C2:E2');
	$objPHPExcel->getActiveSheet()->getStyle('A1:E2')->applyFromArray(
						array(
							'font' => array(
								'size' => 13,								
								'bold' => true
										)
							)
				);
			$objPHPExcel->getActiveSheet()->getStyle('A2')->applyFromArray(
						array(
							'font' => array(
								'size' => 12,
								'italic' => true
	
							),
						)
				);
			$objPHPExcel->getActiveSheet()->getStyle('A1:E2')->applyFromArray(
						array(
							   'alignment' => array(
								'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
								'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
							)
						)
				);
	
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('D4', 'Ngày Tạo: ');
	$objPHPExcel->getActiveSheet()->getStyle('D4')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
	$objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('E4', date('d/m/Y', time()));
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A4', 'Liên hệ: Nguyễn Phạm Thanh Tú/CBKT/Phòng KH-KT');
	$objPHPExcel->setActiveSheetIndex(0)
			->setCellValue('A5', 'Điện thoại/Fax/Email: ');
	$objPHPExcel->getActiveSheet()->getStyle('A3:E4')->applyFromArray(
			array(
				'font' => array(
					'size' => 12,
					
				//'bold' => true
				),
				'alignment' => array(
					'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER
				)
			)
	);

// Try lay du lieu vao bang excel truong hop 1 donvi va 1 congsuat
if ($madv != 'tatca') {// neu don vi khong phai la tat ca
    $db = new database();
    $db->setQuery("select * from don_vi where MA_DON_VI = '" . $madv . "'");
    $re = $db->fetchAll();
    $row = mysql_fetch_array($re);
    $tendv = $row['TEN_DON_VI'];

    //Danh sach may tinh so huu
    $db->setQuery("select * from chi_tiet_so_huu where MA_DON_VI = '" . $madv . "' group by SO_N0");
    $dong = $db->fetchAll();

    $mang = array();
    $i = 0;
    while ($in = mysql_fetch_array($dong)) {
        $mang[$i] = $in['SO_N0'];
        $i++;
    }
    // echo " <br >";
    $mang2 = array();
    //Lay danh sach may don vi dang so huu	
    $k = 0;
    for ($j = 0; $j < $i; $j++) {//vong for 
        $db->setQuery("select * from chi_tiet_so_huu where SO_N0 = '" . $mang[$j] . "' order by NGAY_CHUYEN_DEN desc");
        $truyvan = $db->fetchAll();
        $madvtimdc = mysql_fetch_array($truyvan);
        if ($madvtimdc['MA_DON_VI'] == $madv) {
            $mang2[$k] = $madvtimdc['SO_N0'];
            $k++;
        }
    }//ket thuc for 1
    if (count($mang2) == 0) {//kiem tra don vi co may bien ap ko
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A8', 'Hiện tại đơn vị ' . $tendv . ' không sở hữu máy biến áp nào để thống kê');
    } else { //vòng else kiem tra don vi co may bien ap ko
        if ($loaimay != 'tatca') {
            $dem = 0;
            $tennhasx = "Tất cả";
            $tenloai = "";
            $chuoitruyvan = "";
            $chuoitruyvan = "MA_LOAI = '" . $loaimay . "'";
            $db->setQuery("select * from loai_may where MA_LOAI = '" . $loaimay . "'");
            $laytenloai = $db->fetchAll();
            $ketquatenloai = mysql_fetch_array($laytenloai);
            $tenloai = $ketquatenloai['TEN_LOAI'];

            if ($nhasx != 'tatca') {
                // lay ten nha san xuat
                $db->setQuery("select * from nha_sx where MA_NSX = '" . $nhasx . "'");
                $laytennhasx = $db->fetchAll();
                $ketquatennsx = mysql_fetch_array($laytennhasx);
                $tennhasx = $ketquatennsx['TEN_NSX'];
                $chuoitruyvan = $chuoitruyvan . " and MA_NSX = '" . $nhasx . "'";
            }
            if ($nsx != 'tatca')
                $chuoitruyvan = $chuoitruyvan . " and NAM_SX = '" . $nsx . "'";
            else
                $nsx = "Tất cả";

            for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                $db->setQuery("select * from may_bien_ap where SO_N0 = '" . $mang2[$i] . "'  and " . $chuoitruyvan);
                $truyvan1 = $db->fetchAll();
                $ketqua1 = mysql_fetch_array($truyvan1);
                if ($ketqua1['SO_N0'] != NULL)
                    $dem++;
            }//ket thuc for 2
            // bang cho 1 don vi va 1 nhasx 1 nam sx 1 1oai may
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A6', 'Bảng Thống Kê Theo Năm SX, Nhà SX, Loại Máy')
                    ->setCellValue('A7', 'Tên Đơn Vị')
                    ->setCellValue('B7', 'Loại Máy')
                    ->setCellValue('C7', 'Nhà Sản Xuất')
                    ->setCellValue('D7', 'Năm Sản Xuất')
                    ->setCellValue('E7', 'Số Lượng')
                    ->setCellValue('A8', $tendv)
                    ->setCellValue('B8', $tenloai)
                    ->setCellValue('C8', $tennhasx)
                    ->setCellValue('D8', $nsx)
                    ->setCellValue('E8', $dem)
                    ->setCellValue('A9', 'Tổng số: ' . $dem . ' máy ');
            $objPHPExcel->getActiveSheet()->mergeCells('A9:E9');
            $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
            $objPHPExcel->getActiveSheet()->getStyle('A9')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);

            $objPHPExcel->getActiveSheet()->getStyle('A6:E8')->applyFromArray(
                    array(
                        'alignment' => array(
                            'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                            'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                        )
                    )
            );
        }
        else {//vong else khi tat ca ca loai may
            // bang cho 1 don vi va 1 nhasx 1 nam sx- tat ca 1oai may
            $cellcount = 8;
            $count4merge = 8;
            $tong = 0;
            $chuoitruyvan = "";
            $kiemtra = 0;
            $objPHPExcel->setActiveSheetIndex(0)
                    ->setCellValue('A6', 'Bảng Thống Kê Theo Năm SX, Nhà SX, Loại Máy')
                    ->setCellValue('A7', 'Tên Đơn Vị')
                    ->setCellValue('B7', 'Loại Máy')
                    ->setCellValue('C7', 'Nhà Sản Xuất')
                    ->setCellValue('D7', 'Năm Sản Xuất')
                    ->setCellValue('E7', 'Số Lượng')
                    ->setCellValue('A8', $tendv);

            $db->setQuery("select * from loai_may");
            $layloaimay = $db->fetchAll();
            while ($dong = mysql_fetch_array($layloaimay)) {

                $loaimay = $dong['MA_LOAI'];
                $tenloai = $dong['TEN_LOAI'];
                $dem = 0;
                $tennhasx = "Tất cả";
                $chuoitruyvan = "MA_LOAI = '" . $loaimay . "'";
                if ($nhasx != 'tatca') {
                    // lay ten nha san xuat
                    $db->setQuery("select * from nha_sx where MA_NSX = '" . $nhasx . "'");
                    $laytennhasx = $db->fetchAll();
                    $ketquatennsx = mysql_fetch_array($laytennhasx);
                    $tennhasx = $ketquatennsx['TEN_NSX'];
                    $chuoitruyvan = $chuoitruyvan . " and MA_NSX = '" . $nhasx . "'";
                }
                if ($nsx != 'tatca') {
                    $chuoitruyvan = $chuoitruyvan . " and NAM_SX = '" . $nsx . "'";
                    $namsx = $nsx;
                } else
                    $namsx = "Tất cả";

                for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                    $db->setQuery("select * from may_bien_ap where SO_N0 = '" . $mang2[$i] . "'  and " . $chuoitruyvan);
                    $truyvan1 = $db->fetchAll();
                    $ketqua1 = mysql_fetch_array($truyvan1);
                    if ($ketqua1['SO_N0'] != NULL)
                        $dem++;
                }//ket thuc for 2

                $kiemtra = $kiemtra + $dem;
                $tong = $tong + $dem;
                if ($dem != 0) {

                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('B' . $cellcount, $tenloai)
                            ->setCellValue('C' . $cellcount, $tennhasx)
                            ->setCellValue('D' . $cellcount, $namsx)
                            ->setCellValue('E' . $cellcount, $dem);


                    $cellcount++;
                }
            }

            if ($kiemtra != 0) {
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, 'Tổng số: ' . $tong . ' máy');
                $objPHPExcel->getActiveSheet()->mergeCells('A8:A' . ($cellcount - 1));
                $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':E' . $cellcount);

                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $objPHPExcel->getActiveSheet()->getStyle('A6:E' . ($cellcount - 1))->applyFromArray(
                        array(
                            'alignment' => array(
                                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            )
                        )
                );
            } else {
                $cellcount++;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, "Không tìm thấy kết quả thống kê theo các điều kiện đã chọn");
                $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':E' . $cellcount);

                /* echo "<tr>";
                  echo "<td colspan ='5'>Không tìm thấy kết quả thống kê theo các điều kiện đã chọn </td> " ;

                  echo "</tr>"; */
            }
            //echo "</table>";
        }//vong else khi tat ca ca loai may
    }//vòng else kiem tra don vi co may bien ap ko
}// neu don vi khong phai la tat ca
else {
    // bang cho tat ca don vi va 1 nhasx 1 nam sx- tat ca 1oai may
    $cellcount = 8;
    $db1 = new database();
    $tongmaycacdv = 0;
    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A6', 'Bảng Thống Kê Theo Năm SX, Nhà SX, Loại Máy')
            ->setCellValue('A7', 'Tên Đơn Vị')
            ->setCellValue('B7', 'Loại Máy')
            ->setCellValue('C7', 'Nhà Sản Xuất')
            ->setCellValue('D7', 'Năm Sản Xuất')
            ->setCellValue('E7', 'Số Lượng');


    $db1->setQuery("select * from don_vi as dv, tai_khoan as tk where dv.MA_DON_VI = tk.MA_DON_VI and  tk.QUYEN = 3");

    $tatcadonvi = $db1->fetchAll();
    while ($dongdv = mysql_fetch_array($tatcadonvi)) {
        $tendv = $dongdv['TEN_DON_VI'];
        $madv = $dongdv['MA_DON_VI'];
        //Danh sach may tinh so huu
        $db1->setQuery("select * from chi_tiet_so_huu where MA_DON_VI = '" . $madv . "' group by SO_N0");
        $dong = $db1->fetchAll();

        $mang = array();
        $i = 0;
        while ($in = mysql_fetch_array($dong)) {
            $mang[$i] = $in['SO_N0'];
            $i++;
        }

        $mang2 = array();
        //Lay danh sach may don vi dang so huu	
        $k = 0;
        for ($j = 0; $j < $i; $j++) {//vong for 1
            $db1->setQuery("select * from chi_tiet_so_huu where SO_N0 = '" . $mang[$j] . "' order by NGAY_CHUYEN_DEN desc");
            $truyvan = $db1->fetchAll();
            $madvtimdc = mysql_fetch_array($truyvan);
            if ($madvtimdc['MA_DON_VI'] == $madv) {
                $mang2[$k] = $madvtimdc['SO_N0'];
                $k++;
            }
        }//ket thuc for 1
        if (count($mang2) == 0) {//kiem tra don vi co so huu may bien ap khong
            $dsdv = $dsdv . ", " . $tendv;
        }//kiem tra don vi co so huu may bien ap khong
        else {// vòng else kiem tra don vi co so huu may bien ap khong
            if ($loaimay != 'tatca') {//if loai may khac tat ca
                $dem = 0;
                $tennhasx = "Tất cả";
                $tenloai = "";
                $namsx = "";
                $chuoitruyvan = "";

                $chuoitruyvan = "MA_LOAI = '" . $loaimay . "'";
                $db1->setQuery("select * from loai_may where MA_LOAI = '" . $loaimay . "'");
                $laytenloai = $db1->fetchAll();
                $ketquatenloai = mysql_fetch_array($laytenloai);
                $tenloai = $ketquatenloai['TEN_LOAI'];

                if ($nhasx != 'tatca') {
                    // lay ten nha san xuat

                    $db1->setQuery("select * from nha_sx where MA_NSX = '" . $nhasx . "'");
                    $laytennhasx = $db1->fetchAll();
                    $ketquatennsx = mysql_fetch_array($laytennhasx);
                    $tennhasx = $ketquatennsx['TEN_NSX'];
                    $chuoitruyvan = $chuoitruyvan . " and MA_NSX = '" . $nhasx . "'";
                }
                if ($nsx != 'tatca') {
                    $chuoitruyvan = $chuoitruyvan . " and NAM_SX = '" . $nsx . "'";
                    $namsx = $nsx;
                } else
                    $namsx = "Tất cả";

                for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                    $db1->setQuery("select * from may_bien_ap where SO_N0 = '" . $mang2[$i] . "'  and " . $chuoitruyvan);
                    $truyvan1 = $db1->fetchAll();

                    $ketqua1 = mysql_fetch_array($truyvan1);
                    if ($ketqua1['SO_N0'] != NULL)
                        $dem++;
                }//ket thuc for 2



                $tongmaycacdv = $tongmaycacdv + $dem;

                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, $tendv)
                        ->setCellValue('B' . $cellcount, $tenloai)
                        ->setCellValue('C' . $cellcount, $tennhasx)
                        ->setCellValue('D' . $cellcount, $namsx)
                        ->setCellValue('E' . $cellcount, $dem);
                $cellcount++;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, 'Tổng số: ' . $dem . ' máy');
                $objPHPExcel->getActiveSheet()->getStyle('A' . ($cellcount - 1) . ':E' . ($cellcount - 1))->applyFromArray(
                        array(
                            'alignment' => array(
                                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                            )
                        )
                );
                $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':E' . $cellcount);

                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                $cellcount++;
            }//if loai may khac tat ca
            else {
                $tong = 0;
                $chuoitruyvan = "";
                $kiemtra = 0;

                $count4merge = $cellcount;
                $objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A' . $cellcount, $tendv);

                $db1->setQuery("select * from loai_may");
                $layloaimay = $db1->fetchAll();
                while ($dong = mysql_fetch_array($layloaimay)) {

                    $lmay = $dong['MA_LOAI'];
                    $tloai = $dong['TEN_LOAI'];
                    $dem = 0;
                    $tennhasx = "Tất cả";
                    $chuoitruyvan = "MA_LOAI = '" . $lmay . "'";
                    if ($nhasx != 'tatca') {
                        // lay ten nha san xuat
                        $db1->setQuery("select * from nha_sx where MA_NSX = '" . $nhasx . "'");
                        $laytennhasx = $db1->fetchAll();
                        $ketquatennsx = mysql_fetch_array($laytennhasx);
                        $tennhasx = $ketquatennsx['TEN_NSX'];
                        $chuoitruyvan = $chuoitruyvan . " and MA_NSX = '" . $nhasx . "'";
                    }
                    if ($nsx != 'tatca') {
                        $chuoitruyvan = $chuoitruyvan . " and NAM_SX = '" . $nsx . "'";
                        $namsx = $nsx;
                    } else
                        $namsx = "Tất cả";

                    for ($i = 0; $i < count($mang2); $i++) { //vong for 2
                        $db1->setQuery("select * from may_bien_ap where SO_N0 = '" . $mang2[$i] . "'  and " . $chuoitruyvan);
                        $truyvan1 = $db1->fetchAll();
                        $ketqua1 = mysql_fetch_array($truyvan1);
                        if ($ketqua1['SO_N0'] != NULL)
                            $dem++;
                    }//ket thuc for 2

                    $kiemtra = $kiemtra + $dem;
                    $tongmaycacdv = $tongmaycacdv + $dem;
                    $tong = $tong + $dem;
                    if ($dem != 0) {

                        $objPHPExcel->setActiveSheetIndex(0)
                                ->setCellValue('B' . $cellcount, $tloai)
                                ->setCellValue('C' . $cellcount, $tennhasx)
                                ->setCellValue('D' . $cellcount, $namsx)
                                ->setCellValue('E' . $cellcount, $dem);
                        $cellcount++;
                    }
                }

                if ($kiemtra != 0) {
                    $count4merge_tmp = $cellcount - 1;
                    // trang tri
                    $objPHPExcel->getActiveSheet()->mergeCells('A' . $count4merge . ':A' . $count4merge_tmp);
                    // Set format for tendv
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $count4merge . ':E' . $count4merge_tmp)->applyFromArray(
                            array(
                                'alignment' => array(
                                    'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                                    'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
                                )
                            )
                    );
                   

                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A' . $cellcount, 'Tổng số: ' . $tong . ' máy ');
                    $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':E' . $cellcount);
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setVertical(PHPExcel_Style_Alignment::VERTICAL_CENTER);
                    $objPHPExcel->getActiveSheet()->getStyle('A' . $cellcount)->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_RIGHT);
                    $cellcount++;
                    /* echo "<tr>";
                      echo "<td colspan ='5'>Tổng số: ".$tong." máy</td> " ;
                      echo "</tr>"; */
                } else {
                    $cellcount++;
                    $objPHPExcel->setActiveSheetIndex(0)
                            ->setCellValue('A' . $cellcount, 'Không tìm thấy kết quả thống kê theo các điều kiện đã chọn');
                    $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':E' . $cellcount);
                    /* echo "<tr>";
                      echo "<td colspan ='5'>Không tìm thấy kết quả thống kê theo các điều kiện đã chọn </td> " ;
                      echo "</tr>"; */
                }
            }
        }//vong else kiem tra don vi co so huu may bien ap khong
    }//end while don vi

    $objPHPExcel->setActiveSheetIndex(0)
            ->setCellValue('A' . $cellcount, 'Tổng số tất cả các máy theo điều kiện trên: ' . $tongmaycacdv . ' máy');
    $objPHPExcel->getActiveSheet()->mergeCells('A' . $cellcount . ':E' . $cellcount);


    if ($dsdv != "") {
        $dsdv = ltrim($dsdv, ",");
        $cellcount++;
        $objPHPExcel->setActiveSheetIndex(0)
                ->setCellValue('A' . $cellcount, 'Danh sách các đơn vị không sở hữu MBA:' . $dsdv);
    }
}//end else
//merg dau` bang
$objPHPExcel->getActiveSheet()->mergeCells('A6:E6');
// set autoresize cho dong tieu de bang
$objPHPExcel->getActiveSheet()->getColumnDimension('A')->setWidth(35);
$objPHPExcel->getActiveSheet()->getColumnDimension('B')->setWidth(20);
$objPHPExcel->getActiveSheet()->getColumnDimension('C')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('D')->setWidth(25);
$objPHPExcel->getActiveSheet()->getColumnDimension('E')->setWidth(15);
// vien bang
$objPHPExcel->getActiveSheet()->getStyle('A6:' .
        $objPHPExcel->getActiveSheet()->getHighestColumn() .
        $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
        array(

            'borders' => array(
                'allborders' => array(
                    'style' => PHPExcel_Style_Border::BORDER_THIN,
                    'color' => array('rgb' => '#000000'),
                )
            )
        )
);
// Set format for tableheader
$objPHPExcel->getActiveSheet()->getStyle('A6:E7')->applyFromArray(
        array(
            'font' => array(
                'bold' => true,
                'size' => 13
,
            ),
            'alignment' => array(
                'horizontal' => PHPExcel_Style_Alignment::HORIZONTAL_CENTER,
                'vertical' => PHPExcel_Style_Alignment::VERTICAL_CENTER,
            )


        )
);
		// dinh font cho toan bo file
   		 $objPHPExcel->getActiveSheet()->getStyle('A1:' .
            $objPHPExcel->getActiveSheet()->getHighestColumn() .
            $objPHPExcel->getActiveSheet()->getHighestRow())->applyFromArray(
            array(
                'font' => array(                    
                    'name' => 'Times new Roman'
                )

            )
    );



// Rename worksheet
$objPHPExcel->getActiveSheet()->setTitle('Thống kê');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Redirect output to a client’s web browser (Excel5)
header('Content-Type: application/vnd.ms-excel');
header('Content-Disposition: attachment;filename="thongkeloai.xls"');
header('Cache-Control: max-age=0');
// If you're serving to IE 9, then the following may be needed
header('Cache-Control: max-age=1');

// If you're serving to IE over SSL, then the following may be needed
header('Expires: Mon, 26 Jul 1997 05:00:00 GMT'); // Date in the past
header('Last-Modified: ' . gmdate('D, d M Y H:i:s') . ' GMT'); // always modified
header('Cache-Control: cache, must-revalidate'); // HTTP/1.1
header('Pragma: public'); // HTTP/1.0

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
$objWriter->save('php://output');
exit;
?>